package com.example.mapp.buildSql;

import java.util.Map;

public class IndexEchartsMappSql {

    /**
     * 辖区内电梯类型分布
     *
     * @return
     */
    public String countTypeDistribType() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT et.id ,et.type_name AS 'name',COUNT(e.elevator_type_id) AS 'count' FROM elevator_type et ");
        sb.append("LEFT JOIN elevator e ON e.elevator_type_id = et.id GROUP BY et.id order by COUNT(e.elevator_type_id) desc  ");
        return sb.toString();
    }

    /**
     * 市辖区内电梯类型分布
     *
     * @return
     */
    public String countTypeDistribTypeCity(String id) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT et.id ,et.type_name AS 'name',COUNT(e.elevator_type_id) AS 'count' FROM elevator_type et ");
        sb.append("LEFT JOIN (select * from elevator r where r.branch_office in (select id from party where parent_id in (select id from party where parent_id=#{id})) and r.is_stop is not null) e ON e.elevator_type_id = et.id GROUP BY et.id order by COUNT(e.elevator_type_id) desc  ");
        return sb.toString();
    }

    /**
     * 局分局区内电梯类型分布
     *
     * @return
     */
    public String countTypeDistribTypeArea(String id) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT et.id ,et.type_name AS 'name',COUNT(e.elevator_type_id) AS 'count' FROM elevator_type et ");
        sb.append("LEFT JOIN (select * from elevator r where r.branch_office in ((select id from party where parent_id=#{id})) and r.is_stop is not null) e ON e.elevator_type_id = et.id GROUP BY et.id order by COUNT(e.elevator_type_id) desc  ");
        return sb.toString();
    }

    /**
     *单位排行-->市数据
     */
    public String maintenanceRanksCity(Map map) {
        StringBuffer sb = new StringBuffer();
//        String partyId = map.get("partyId").toString();
        int i = (int) map.get("type");
        String param = map.get("param").toString();

        sb.append(" select (@rowNum:=@rowNum+1) as rowNo, partyName ,mark from ( ");
        sb.append(" select  ");
        sb.append(" party_name as 'partyName',  ");
        sb.append(" mark as 'mark'  ");
        sb.append(" from sys_user er  ");
        sb.append(" left join party_user pr ON pr.user_id = er.id  ");
        sb.append(" left join party py ON py.id = pr.party_id  ");
        sb.append(" where py."+param+ " in (select id from party p where p.parent_id=#{partyId}) and party_type="+i+"  ");
        sb.append(" and er.type='party'  order by  er.mark desc  ");
        sb.append(" )party,(SELECT @rowNum:=0) b   limit 10  ");
        return sb.toString();
    }

    /**
     *用户排行-->市数据
     */
    public String maintenanceUserRanksCity(Map map) {
        StringBuffer sb = new StringBuffer();
//        String partyId = map.get("partyId").toString();
        int i = (int) map.get("type");
        String param = map.get("param").toString();

        sb.append(" select (@rowNum:=@rowNum+1) as rowNo, userName ,IFNULL(mark,0) as mark ,partyName, ");
        sb.append(" case when IFNULL(mark,0) <200 then '初出茅庐' ");
        sb.append(" when IFNULL(mark,0) >=200 and IFNULL(mark,0) <500 then '小有成就'  ");
        sb.append(" when IFNULL(mark,0) >=500 and IFNULL(mark,0) <1000 then '电梯卫士'  ");
        sb.append(" else '电梯大咖' end as 'name' ");
        sb.append(" from (  ");
        sb.append(" select  ");
        sb.append(" py.party_name as 'partyName',  ");
        sb.append(" user_name as 'userName',  ");
        sb.append(" mark as 'mark' ");
        sb.append(" from sys_user er  ");
        sb.append(" left join party_user pr ON pr.user_id = er.id  ");
        sb.append(" left join party py ON py.id = pr.party_id  ");
        sb.append(" where py."+param+ " in (select id from party p where p.parent_id=#{partyId})  ");
        sb.append(" and py.party_type="+i+" ");
        sb.append(" and er.type='user'  ");
        sb.append(" order by  er.mark desc  ");
        sb.append(" ) users,(SELECT @rowNum:=0) b   limit 10 ");
        return sb.toString();
    }




}
